package DB;

import Model.Product;

import java.sql.*;
import java.util.ArrayList;

public class ProductDB implements ProductDBIF {

    private Connection conn;
    private PreparedStatement prepStatement;

    public ProductDB() {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public Product findProduct(int id) {
        Product product;
        product = findWithPreQuery(id);
        return product;
    }

    @Override
    public int insertProduct(Product product) {
        int rc = -1;
        PreparedStatement pstmt = null;

        String insert = "INSERT INTO Product (id, name, purchasePrice, salesPrice, rentPrice, " +
                "countryOfOrigin, minStock, type, supplier)" + "values (?,?,?,?,?,?,?,?,?)";

        System.out.println(insert);

        try {
            pstmt = conn.prepareStatement(insert);
            pstmt.setInt(1, product.getId());
            pstmt.setString(2, product.getName());
            pstmt.setFloat(3, product.getPurchasePrice());
            pstmt.setFloat(4, product.getSalesPrice());
            pstmt.setFloat(5, product.getRentPrice());
            pstmt.setString(6, product.getCountryOfOrigin());
            pstmt.setInt(7, product.getMinStock());
            pstmt.setString(8, product.getType());
            pstmt.setInt(9, product.getSupplier());
            rc = pstmt.executeUpdate();
        }
        catch (SQLException sqlE) {
            System.out.println("SQL Error: " + sqlE.getMessage());
        }
        catch (Exception e) {
            e.getMessage();
        }

        return rc;
    }

    @Override
    public ArrayList<Product> getAllProducts(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int deleteProduct(int id) {
        int rc=-1;

        String query="DELETE FROM product WHERE id = " +
                id;
        System.out.println(query);
        try{
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex){
            System.out.println("Delete exception in product db: "+ex);
        }
        return(rc);
    }

    @Override
    public ArrayList<Product> findProducts(String name, boolean retrieveAssociation) {
        String wClause = "name like '%" + name + "%'";
        System.out.println("Search product: " + wClause);
        return miscWhere(wClause, retrieveAssociation);
    }

    public Product findProduct(String attValue, boolean retriveAssociation) {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("Search product: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    private Product singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Product proObj = new Product();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() ){
                proObj = buildProduct(results);
                //assocaition is to be build
            }
            stmt.close();
        }

        catch(Exception e){
            System.out.println("Query exception: " +e);
        }

        return proObj;
    }

    private ArrayList<Product> miscWhere (String wClause, boolean retrieveAssociation) {
        ResultSet results;

        ArrayList<Product> list = new ArrayList<Product>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Product proObj;
                proObj = buildProduct(results);
                list.add(proObj);
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    private String buildQuery (String wClause) {
        String query = "SELECT id, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, type, supplier FROM Product";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Prepared statement
    private PreparedStatement getPreparedStmt()
    {
        try{
            String query = "select id, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, type, supplier from product" +
                    " where id = ?";
            System.out.println("Prepared statement: " + query);
            prepStatement = conn.prepareStatement(query);

        }
        catch(Exception e){
            e.getMessage();
        }
        return prepStatement;
    }

    //Build product
    private Product buildProduct(ResultSet results)
    {
        Product product = new Product();

        try {
            product.setId(results.getInt("id"));
            product.setName(results.getString("name"));
            product.setPurchasePrice(results.getFloat("purchasePrice"));
            product.setSalesPrice(results.getFloat("salesPrice"));
            product.setRentPrice(results.getFloat("rentPrice"));
            product.setCountryOfOrigin(results.getString("countryOfOrigin"));
            product.setMinStock(results.getInt("minStock"));
            product.setType(results.getString("type"));
            product.setSupplier(results.getInt("supplier"));
        }
        catch(Exception e)
        {
            System.out.println("Error in building product" + e);
        }
        return product;
    }

    public Product findWithPreQuery(int id) {
        if(prepStatement == null) {
            prepStatement = getPreparedStmt();
        }

        Product product = null;
        ResultSet results;

        try {
            prepStatement.setInt(1, id);
            results = prepStatement.executeQuery();

            if(results.next()) {
                product = buildProduct(results);
            }
        }
        catch (Exception e) {
            e.getMessage();
        }

        return product;
    }

    public int productGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Product");

        nextId = nextId +1;

        return nextId;
    }
}
